 
			 

<!-- This script will create all tables needed in the Open Doors project -->

<!-- It will need the following
- A data base HOST to connect to
- A USER account with administrator privileges
- A PASSWORD for the user account
-->

<?php

/*
  This file is part of Open-Doors.

  Open-Doors is free software: you can redistribute it and/or modify
  it under the terms of the GNU General Public License as published by
  the Free Software Foundation, either version 3 of the License, or
  (at your option) any later version.
  
  Open-Doors is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  GNU General Public License for more details.
  
  You should have received a copy of the GNU General Public License
  along with Open-Doors.  If not, see <http://www.gnu.org/licenses/>.

  Authors: Rune Thorbek & Araz B. Makoo
*/ 
		     // we include the db_connect to get the defines
include 'db_connect.php';
include 'functions.php';

/* Check if user is logged in, otherwise kill the request. */
// sec_session_start();
// if(login_check($mysqli) != true) {
//   die("Authorization needed");
// }
// if ($_SESSION['username'] != "admin") {
//   die("You need to be admin.");
// }


// These are configured for admin on user on mysql database
$ADMIN_USER = 'root';
$ADMIN_PASSWORD = 'password';

// These are password for the login to the website 
// NOTE!!!: Remeber to change them!!
$WEBSITE_ADMIN_PASSWORD = "password";
$WEBSITE_USER_PASSWORD  = "password";

function query_db($con, $query, $msg) {
  if (mysqli_query($con,$query))
    {
      echo $msg." : SUCCESS<br />";
    } else {
    echo $msg." : FAILURE<br />";
    echo "MySQL ERROR : ".mysqli_error($con)."<br />";
    die("ERROR : @ query : ".$query);
  }
}
function query_db_no_msg($con, $query) {
  if (mysqli_query($con,$query))
    {

    } else {
    echo $msg." : FAILURE<br />";
    echo "MySQL ERROR : ".mysqli_error($con)."<br />";
    die("ERROR : @ query : ".$query);
  }
}

$con = mysqli_connect(HOST, $ADMIN_USER, $ADMIN_PASSWORD);
if (mysqli_connect_errno())
  {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    die("ERROR: MySQL connection error");
  }

$con->set_charset("utf8");


// Should we just remove if exists??? Or should we test and warn??
// While debugging it is nice that it does it
$sql="DROP DATABASE IF EXISTS `".DATABASE."`";
query_db($con, $sql, "Removing database ".DATABASE);

$sql="SELECT * FROM mysql.user WHERE user=ˋ".USER."ˋ";
$res=mysqli_query($con, $sql);
if (mysqli_num_rows($res) > 0) {
  echo "USER ".USER." exists<br />";
  $sql="DROP USER ˋ".USER."ˋ@ˋ".HOST."ˋ";
  query_db($con, $sql, "Removing user ".USER);
} else {
  echo "USER ".USER." does not exists<br />";
}

// We start creating stuff here.
$sql="CREATE DATABASE ".DATABASE;
query_db($con, $sql, "Create database ".DATABASE);

$sql="CREATE USER '".USER."'@'".HOST."' IDENTIFIED BY '".PASSWORD."'";
//query_db($con, $sql, "Create user ".USER);

$sql="GRANT SELECT, INSERT, UPDATE, DELETE ON `".DATABASE."`.* TO '".USER."'@'".HOST."'";
query_db($con, $sql, "Grant access for user ".USER);

$sql="USE ".DATABASE;
query_db($con, $sql, "Select database ".DATABASE);

$sql="CREATE TABLE `".DATABASE."`.`members` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `username` VARCHAR(30) NOT NULL, `email` VARCHAR(50) NOT NULL, `password` CHAR(128) NOT NULL, `salt` CHAR(128) NOT NULL) ENGINE = InnoDB;";
query_db($con, $sql, "Creating table members");

$sql="CREATE TABLE `".DATABASE."`.`login_attempts` (`user_id` int(11) NOT NULL, `time` VARCHAR(30) NOT NULL) ENGINE=InnoDB";
query_db($con, $sql, "Creating table login_attempts");

// The user password is: 6ZaxN2Vzm9NUJT2y

$sql="INSERT INTO `".DATABASE."`.`members` VALUES(1, 'test_user', 'test@example.com', '00807432eae173f652f2064bdca1b61b290b52d40e429a7d295d76a71084aa96c0233b82f1feac45529e0726559645acaed6f3ae58a286b9f075916ebf66cacc', 'f9aab579fc1b41ed0c44fe4ecdbfcdb4cb99b9023abb241a6db833288f4eea3c02f76e0d35204a8695077dcf81932aa59006423976224be0390395bae152d4ef')";
query_db($con, $sql, "Creating test_user in table members");


// This function makes an sql query to create user username
function create_user_sql($username, $email, $password) {
  $hashed_password = hash('sha512', $password);
  $salt = hash('sha512', rand());
  $db_password = hash('sha512', $hashed_password.$salt);
  $sql_query = "INSERT INTO `".DATABASE."`.`members` (`username`, `email`, `password`, `salt`) VALUES('".$username."','".$email."','".$db_password."','".$salt."')";
  return $sql_query;
}

// example call
$sql=create_user_sql("admin", "admin@example.com", $WEBSITE_ADMIN_PASSWORD);
query_db($con, $sql, "Creating admin in table members");

$sql=create_user_sql("user", "user@example.com", $WEBSITE_USER_PASSWORD);
query_db($con, $sql, "Creating user in table members");


// We create a table for the collectors
// please note, it is just an example.

/*
  collectors:
  - uid
  - number
  - first_name
  - last_name
  - email_address
  - address
  - mobile_number
  - phone_number
  - group_uid
  - campaign_uid
  - venue_uid
*/
$sql="CREATE TABLE `".DATABASE."`.`collectors` (`uid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `number` INT, `first_name` VARCHAR(128) NOT NULL, `last_name` VARCHAR(128) NOT NULL, `email_address` VARCHAR(128) NOT NULL, `address` VARCHAR(256), `mobile_number` VARCHAR(12), `phone_number` VARCHAR(12), `group_uid` INT, `campaign_uid` INT, `venue_uid` INT, INDEX uid_idx (uid), INDEX group_uid_idx (group_uid), INDEX campaign_uid_idx (campaign_uid), INDEX venue_uid_idx (venue_uid), INDEX number_idx (number), INDEX first_name_idx (first_name), INDEX last_name_idx (last_name), INDEX email_address_idx (email_address), INDEX address_idx (address), INDEX mobile_number_idx (mobile_number), INDEX phone_number_idx (phone_number)) ENGINE = InnoDB;";
query_db($con, $sql, "Creating table collectors");

/*
  groups:
  - uid
  - no_collection_boxes
  - returned_collection_boxes
  - email_wish
  - same_route
  - amount_bank_notes
  - amount_coins
  - amount_app
  - comment
  - collects_with_field // only used when parsing csv files
*/
$sql="CREATE TABLE `".DATABASE."`.`groups` (`uid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `no_collection_boxes` INT, `returned_collection_boxes` INT, `email_wish` BOOLEAN, `same_route` BOOLEAN, `amount_bank_notes` DECIMAL(10,2), `amount_coins` DECIMAL(10,2), `amount_app` DECIMAL(10,2), `comment` VARCHAR(512), `collects_with_field` VARCHAR(512), INDEX uid_idx (uid), INDEX no_collection_boxes_idx (no_collection_boxes), INDEX returned_collection_boxes_idx (returned_collection_boxes), INDEX email_wish_idx (email_wish), INDEX same_route_idx (same_route), INDEX amount_bank_notes_idx (amount_bank_notes), INDEX amount_coins_idx (amount_coins), INDEX amount_app_idx (amount_app), INDEX comment_idx (comment), INDEX collects_with_field_idx (collects_with_field)) ENGINE = InnoDB;";
query_db($con, $sql, "Creating table groups");

/* 
   routes:
   - uid
   - route_number
   - group_uid
   - venue_uid
*/
$sql="CREATE TABLE `".DATABASE."`.`routes` (`uid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `route_number` INT, `group_uid` INT, `venue_uid` INT, INDEX uid_idx (uid), INDEX group_uid_idx (group_uid), INDEX venue_uid_idx (venue_uid), INDEX route_number_idx (route_number)) ENGINE = InnoDB;";
query_db($con, $sql, "Creating table routes");

/*
  campaigns:
  - uid
  - name
*/
$sql="CREATE TABLE `".DATABASE."`.`campaigns` (`uid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(128), INDEX uid_idx (uid), INDEX name_idx (name)) ENGINE = InnoDB;";
query_db($con, $sql, "Creating table campaigns");

/*
  venues:
  - uid
  - name
  - address
  - code
*/
$sql="CREATE TABLE `".DATABASE."`.`venues` (`uid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(128), address VARCHAR(256), code VARCHAR(128), INDEX uid_idx (uid), INDEX name_idx (name), INDEX address_idx (address), INDEX code_idx (code)) ENGINE = InnoDB;";
query_db($con, $sql, "Creating table venues");


function createCollectorSQLQuery($data) {
  $sql = "INSERT INTO `collectors` (number, last_name, first_name, address, mobile_number, phone_number, email_address, group_uid, campaign_uid, venue_uid) VALUES (".$data[0].",'".$data[1]."','".$data[2]."','".$data[3]."','".$data[4]."','".$data[5]."','".$data[6]."', ".$data[7].", ".$data[8].", ".$data[9].")";
  return $sql;
}

for ($i = 0; $i < 10; $i += 1) {
  $data = array($i, "Thorbek".$i, "Rune".$i, "Carl Th. Dreyers Vej 116, 2500 Valby", "22433888", "", "runethorbek@gmail.com", 1, 1, 1);
  $sql = createCollectorSQLQuery($data);
  //query_db($con, $sql, "Inserting a collector ".$i);
}

function createSQLGroupQuery($data) {
  $sql = "INSERT INTO `groups` (no_collection_boxes, returned_collection_boxes, email_wish, same_route, amount_coins, amount_bank_notes, amount_app, comment) VALUES (".$data[1].",".$data[2].",".$data[3].",".$data[4].",".$data[5].",".$data[6].",".$data[7].",'".$data[8]."')";
  return $sql;
}

$data = array("Gruppe Team 1", "2", "0", "true", "false", "0", "0", "0", "This is a comment");
$sql = createSQLGroupQuery($data);
//query_db($con, $sql, "Inserting a group");
$data = array("Team 32 plus", "3", "2", "false", "true", "10", "20", "30", "This is a comment");
$sql = createSQLGroupQuery($data);
//query_db($con, $sql, "Inserting a group");

$sql = "INSERT INTO `routes` (route_number, group_uid, venue_uid) VALUES (18001,1, 1)";
//query_db($con, $sql, "Inserting a route");

function createVenueSQL($data) {
  $sql = "INSERT INTO `venues` (code, name) VALUES ('".$data[0]."', '".$data[1]."')";
  return $sql;
}

function insertVenue($con, $code, $name) {
  $data = array($code, $name);
  $sql  = createVenueSQL($data);
  query_db($con, $sql, "Inserting a venue");
}

function insertRutes($con, $venue, $from, $to) {
  echo "Inserting rutes for ".$venue;
  for ($id = $from; $id <= $to; $id++) {
    $sql = "INSERT INTO `routes` (route_number, venue_uid) VALUES (".$id.",(SELECT uid FROM `venues` WHERE name='".$venue."'))";
    query_db_no_msg($con, $sql);
  }
  echo "<br>";
}

function insertVenueAndRutes($con, $code, $name, $from, $to) {
  insertVenue($con, $code, $name);
  insertRutes($con, $name, $from, $to);
}

insertVenueAndRutes($con, 13, "Amager Nord",    13001, 13150);
insertVenueAndRutes($con, 14, "Amager Syd",     14001, 14135);
insertVenueAndRutes($con, 17, "Bispebjerg",     17001, 17139);
insertVenueAndRutes($con, 18, "Brønshøj",       18001, 18109);
insertVenueAndRutes($con, 12, "City",           12001, 12151);
insertVenueAndRutes($con, 16, "Nørrebro",       16001, 16189);
insertVenueAndRutes($con, 20, "Valby",          20001, 20133);
insertVenueAndRutes($con, 19, "Vanløse",        19001, 19097);
insertVenueAndRutes($con, 15, "Vesterbro",      15001, 15204);
insertVenueAndRutes($con, 10, "Indre Østerbro", 10001, 10141);
insertVenueAndRutes($con, 11, "Ydre Østerbro",  11001, 11107);
insertVenueAndRutes($con, 36, "Frederiksberg",  36001, 36034);
insertVenueAndRutes($con, 21, "Dragør",         21001, 21047);
insertVenueAndRutes($con, 22, "Tårnby",         22001, 22118);

?>